package day02;

import exception.DataAccessException;
import util.JdbcUtil;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@WebServlet("/list")
public class EmpListServlet extends HttpServlet {

    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response) throws IOException {
        System.out.println("EmpListServlet.service()");
        response.setContentType("text/html;charset=utf-8");

        PrintWriter out=response.getWriter();


        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            con= JdbcUtil.getConnection();

            con.setAutoCommit(false);


            String sql=new StringBuffer()
                    .append("select * ")
                    .append("from t_emp ")
                    .toString();

            ps=con.prepareStatement(sql);

            rs=ps.executeQuery();


            out.print("<!DOCTYPE html>");
            out.print("<html lang=\"en\">");
            out.print("    <head>");
            out.print("        <meta charset=\"UTF-8\">");
            out.print("        <title>Title</title>");
            out.print("    </head>");
            out.print("    <body>");
            out.print("        <table border=\"1\" width=\"50%\">");
            out.print("            <tr>");
            out.print("                <th>序号</th>");
            out.print("                <th>姓名</th>");
            out.print("                <th>工资</th>");
            out.print("                <th>操作</th>");
            out.print("            </tr>");

            int i=1;
            while(rs.next()) {
                Integer id=rs.getInt("id");
                String name=rs.getString("name");
                Double salary=rs.getDouble("salary");

                out.print("            <tr>");
                out.print("                <td>"+(i++)+"</td>");
                out.print("                <td>");
                out.print("                     <a href='" + request.getContextPath() + "/showModify?id=" + id + "'>" + name + "</a>");
                out.print("                </td>");
                out.print("                <td>"+salary+"</td>");
                out.print("                <td>");
                out.print("                    <a href=\"" + request.getContextPath() + "/remove?id=" + id + "\" onclick=\"return confirm('确认是否删除？');\">删除</a>");
                out.print("                </td>");
                out.print("            </tr>");
            }
            out.print("        </table>");
            out.print("    </body>");
            out.print("</html>");



            con.commit();

        } catch (Exception e){
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
            throw new DataAccessException("数据访问失败",e);
        } finally {
            JdbcUtil.close(con,ps,rs);
        }








    }
}
